package com.seu.vcampus.dao;

import com.seu.vcampus.vo.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据访问类
 * 负责用户数据的CRUD操作
 * 
 * @author 虚拟校园开发团队
 * @since 1.0.0
 */
public class UserDao {
    private final Connection connection;
    
    public UserDao(Connection connection) {
        this.connection = connection;
        initializeTable();
    }
    
    /**
     * 初始化用户表和权限表
     */
    private void initializeTable() {
        try (Statement stmt = connection.createStatement()) {
            // 创建用户表
            String createUserTableSQL = "CREATE TABLE IF NOT EXISTS users (" +
                    "user_id VARCHAR(50) PRIMARY KEY, " +
                    "username VARCHAR(50) NOT NULL UNIQUE, " +
                    "password VARCHAR(255) NOT NULL, " +
                    "email VARCHAR(100), " +
                    "phone VARCHAR(20), " +
                    "user_type VARCHAR(20) NOT NULL, " +
                    "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
                    "last_login TIMESTAMP, " +
                    "is_active BOOLEAN DEFAULT TRUE)";
            stmt.execute(createUserTableSQL);
            
            // 创建用户权限表
            String createPermissionTableSQL = "CREATE TABLE IF NOT EXISTS user_permissions (" +
                    "user_id VARCHAR(50) NOT NULL, " +
                    "permission VARCHAR(50) NOT NULL, " +
                    "PRIMARY KEY (user_id, permission), " +
                    "FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE)";
            stmt.execute(createPermissionTableSQL);
            
            // 插入默认管理员用户
            if (getUserCount() == 0) {
                insertDefaultAdmin();
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to initialize user tables", e);
        }
    }
    
    /**
     * 插入默认管理员用户
     */
    private void insertDefaultAdmin() {
        // 超级管理员
        User admin = new User("admin001", "admin", "admin123", User.UserType.ADMIN);
        save(admin);
        
        // 为超级管理员添加所有权限
        addPermission("admin001", "USER_MANAGE");
        addPermission("admin001", "BOOK_MANAGE");
        addPermission("admin001", "BORROW_MANAGE");
        addPermission("admin001", "SYSTEM_SETTING");
        addPermission("admin001", "REPORT_VIEW");
        addPermission("admin001", "FINANCE_MANAGE");
        
        // 图书馆管理员
        User librarian = new User("lib001", "librarian", "lib123", User.UserType.LIBRARY_ADMIN);
        librarian.setEmail("librarian@vcampus.edu");
        librarian.setPhone("13800138000");
        save(librarian);
        
        // 为图书馆管理员添加图书管理权限
        addPermission("lib001", "BOOK_MANAGE");
        addPermission("lib001", "BORROW_MANAGE");
        addPermission("lib001", "REPORT_VIEW");
        
        // 系统管理员
        User sysAdmin = new User("sys001", "sysadmin", "sys123", User.UserType.SYSTEM_ADMIN);
        sysAdmin.setEmail("sysadmin@vcampus.edu");
        sysAdmin.setPhone("13800138001");
        save(sysAdmin);
        
        // 为系统管理员添加系统管理权限
        addPermission("sys001", "USER_MANAGE");
        addPermission("sys001", "SYSTEM_SETTING");
        addPermission("sys001", "REPORT_VIEW");
        
        // 财务管理员
        User financeAdmin = new User("finance001", "finance", "finance123", User.UserType.FINANCE_ADMIN);
        financeAdmin.setEmail("finance@vcampus.edu");
        financeAdmin.setPhone("13800138002");
        save(financeAdmin);
        
        // 为财务管理员添加财务管理权限
        addPermission("finance001", "FINANCE_MANAGE");
        addPermission("finance001", "REPORT_VIEW");
    }
    
    /**
     * 保存用户
     * @param user 用户对象
     * @return 是否保存成功
     */
    public boolean save(User user) {
        // 检查用户是否存在
        User existingUser = findById(user.getUserId());
        
        if (existingUser == null) {
            // 插入新用户
            String sql = "INSERT INTO users (user_id, username, password, email, phone, user_type, is_active) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?)";
            
            try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                pstmt.setString(1, user.getUserId());
                pstmt.setString(2, user.getUsername());
                pstmt.setString(3, user.getPassword());
                pstmt.setString(4, user.getEmail());
                pstmt.setString(5, user.getPhone());
                pstmt.setString(6, user.getUserType().name());
                pstmt.setBoolean(7, user.isActive());
                
                return pstmt.executeUpdate() > 0;
            } catch (SQLException e) {
                throw new RuntimeException("Failed to save user", e);
            }
        } else {
            // 更新现有用户
            String sql = "UPDATE users SET username=?, password=?, email=?, phone=?, user_type=?, is_active=? " +
                    "WHERE user_id=?";
            
            try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2, user.getPassword());
                pstmt.setString(3, user.getEmail());
                pstmt.setString(4, user.getPhone());
                pstmt.setString(5, user.getUserType().name());
                pstmt.setBoolean(6, user.isActive());
                pstmt.setString(7, user.getUserId());
                
                return pstmt.executeUpdate() > 0;
            } catch (SQLException e) {
                throw new RuntimeException("Failed to update user", e);
            }
        }
    }
    
    /**
     * 根据用户ID查找用户
     * @param userId 用户ID
     * @return 用户对象，不存在返回null
     */
    public User findById(String userId) {
        String sql = "SELECT * FROM users WHERE user_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToUser(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to find user by ID", e);
        }
    }
    
    /**
     * 根据用户名查找用户
     * @param username 用户名
     * @return 用户对象，不存在返回null
     */
    public User findByUsername(String username) {
        String sql = "SELECT * FROM users WHERE username = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToUser(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to find user by username", e);
        }
    }
    
    /**
     * 获取所有用户
     * @return 用户列表
     */
    public List<User> findAll() {
        String sql = "SELECT * FROM users ORDER BY create_time DESC";
        List<User> users = new ArrayList<>();
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
            return users;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to find all users", e);
        }
    }
    
    /**
     * 删除用户
     * @param userId 用户ID
     * @return 是否删除成功
     */
    public boolean delete(String userId) {
        String sql = "DELETE FROM users WHERE user_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to delete user", e);
        }
    }
    
    /**
     * 更新最后登录时间
     * @param userId 用户ID
     */
    public void updateLastLogin(String userId) {
        String sql = "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE user_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("Failed to update last login", e);
        }
    }
    
    /**
     * 获取用户数量
     * @return 用户总数
     */
    public int getUserCount() {
        String sql = "SELECT COUNT(*) FROM users";
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            return rs.next() ? rs.getInt(1) : 0;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to get user count", e);
        }
    }
    
    /**
     * 搜索用户
     * @param keyword 搜索关键词（用户名、邮箱、电话）
     * @param userType 用户类型，null表示全部类型
     * @param active 状态，null表示全部状态
     * @return 符合条件的用户列表
     */
    public List<User> searchUsers(String keyword, User.UserType userType, Boolean active) {
        StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");
        List<Object> params = new ArrayList<>();
        
        if (keyword != null && !keyword.trim().isEmpty()) {
            sql.append(" AND (username LIKE ? OR email LIKE ? OR phone LIKE ?)");
            String likeKeyword = "%" + keyword + "%";
            params.add(likeKeyword);
            params.add(likeKeyword);
            params.add(likeKeyword);
        }
        
        if (userType != null) {
            sql.append(" AND user_type = ?");
            params.add(userType.name());
        }
        
        if (active != null) {
            sql.append(" AND is_active = ?");
            params.add(active);
        }
        
        sql.append(" ORDER BY create_time DESC");
        
        List<User> users = new ArrayList<>();
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql.toString())) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapResultSetToUser(rs));
                }
            }
            
            return users;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to search users", e);
        }
    }
    
    /**
     * 将ResultSet映射为User对象
     * @param rs 结果集
     * @return User对象
     * @throws SQLException SQL异常
     */
    private User mapResultSetToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setUserId(rs.getString("user_id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setEmail(rs.getString("email"));
        user.setPhone(rs.getString("phone"));
        user.setUserType(User.UserType.valueOf(rs.getString("user_type")));
        user.setCreateTime(rs.getTimestamp("create_time"));
        user.setLastLogin(rs.getTimestamp("last_login"));
        user.setActive(rs.getBoolean("is_active"));
        return user;
    }
    
    /**
     * 为用户添加权限
     * @param userId 用户ID
     * @param permission 权限名称
     * @return 是否添加成功
     */
    public boolean addPermission(String userId, String permission) {
        String sql = "INSERT INTO user_permissions (user_id, permission) VALUES (?, ?)";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            pstmt.setString(2, permission);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to add permission", e);
        }
    }
    
    /**
     * 删除用户权限
     * @param userId 用户ID
     * @param permission 权限名称
     * @return 是否删除成功
     */
    public boolean removePermission(String userId, String permission) {
        String sql = "DELETE FROM user_permissions WHERE user_id = ? AND permission = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            pstmt.setString(2, permission);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to remove permission", e);
        }
    }
    
    /**
     * 获取用户的所有权限
     * @param userId 用户ID
     * @return 权限列表
     */
    public List<String> getUserPermissions(String userId) {
        List<String> permissions = new ArrayList<>();
        String sql = "SELECT permission FROM user_permissions WHERE user_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                permissions.add(rs.getString("permission"));
            }
            return permissions;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to get user permissions", e);
        }
    }
    
    /**
     * 检查用户是否拥有某个权限
     * @param userId 用户ID
     * @param permission 权限名称
     * @return 是否拥有权限
     */
    public boolean hasPermission(String userId, String permission) {
        String sql = "SELECT COUNT(*) FROM user_permissions WHERE user_id = ? AND permission = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            pstmt.setString(2, permission);
            ResultSet rs = pstmt.executeQuery();
            
            return rs.next() && rs.getInt(1) > 0;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to check permission", e);
        }
    }
}